package com.sxhuayuan.parking.dao;

import com.sxhuayuan.parking.entity.Agent;
import com.sxhuayuan.parking.entity.ProfitStatistics;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import javax.transaction.Transactional;
import java.time.LocalDate;
import java.util.List;

/**
 * 代理分润统计dao层
 * 
 * @author 何龙
 * @create 2019-04-25 14:13
 */
public interface ProfitStatisticsDao extends BaseDao<ProfitStatistics, Long> {

	/** 查询代理前一天分润统计 */
	@Query("select p from ProfitStatistics p where p.agent = ?1 and p.statistisDate = ?2")
	List<ProfitStatistics> findByAgentAndToday(Agent agent, LocalDate date);

	/** 根据代理和日期查询分润统计 */
	ProfitStatistics findByAgentAndStatistisDate(Agent agent, LocalDate date);

	/** 根据代理id删除代理对应的分润统计 */
	@Transactional
	@Modifying
	@Query("delete from ProfitStatistics p where p.agent.id=?1")
	void deleteByAgentId(Long id);

	/** 根据代理与月份统计代理月份润 */
	@Query(value = "select sum(s.total_money),sum(s.total_count),sum(s.weixin_money),sum(s.weixin_count),sum(s.alipay_money),sum(s.alipay_count),sum(s.total_profit),sum(s.weixin_profit),sum(s.alipay_profit),sum(s.total_original_profit),sum(s.weixin_original_profit),sum(s.alipay_original_profit),date_format(s.statistis_date,'%Y-%m') d from hypk_profit_statistics s where s.agent_id=?1 group by d having d=?2", nativeQuery = true)
	List<Object> countMonthlyProfitByAgentAndMonth(Long id, String month);

	/** 根据代理与月份查询代理这个月的分润详情 */
	@Query("select p from ProfitStatistics p where p.agent=?1 and p.statistisDate>= ?2 and p.statistisDate<= ?3 order by p.statistisDate")
	List<ProfitStatistics> findByAgentAndMonth(Agent agent, LocalDate minDate, LocalDate maxDate);
}
